Present invention embodiments are related to systems, methods and computer program products for reducing an execution cost of complex case expressions in a database management system language statement.
With increasing use of real-time analytics, Extract, Transfer and Load (ETL) processes that use database management systems are being replaced with in-database transformations within database management system language statements such as, for example, SQL statements. One example of such an in-database transformation are complex CASE expressions coded in a SQL SELECT list to perform data transformations that were previously performed within an ETL process.
An ETL process introduces data latency. By moving the data transformations to a SQL statement, analytics can occur against real-time data. Thus, instead of executing a data transformation once, during ETL, and having queries execute on stale data, executing the data transformation in a SQL statement allows the data transformation to occur on real-time data. However, complex CASE expressions introduce increased execution cost for data transformations in SQL statements.
Due to limitations within a SQL statement regarding sharing of an expression (unless, for example, the expression is coded in a view or table expression and then referenced multiple times in a referencing SELECT statement), duplication of a complete CASE expression in a single SQL statement is not uncommon. Duplication of subsets of a CASE expression or inverse comparisons referenced in separate CASE expressions also are not uncommon. Further, CASE expressions created by SQL code generators also cause duplication.